--item sequence
drop sequence item_no_seq
create sequence item_no_seq;
-- item table
create table java_item(
   item_no number primary key,
   item_name varchar2(30) not null,
   item_price number not null,
   item_info varchar2(200) not null,
   member_id varchar2(200) not null,
   item_upload_date date not null,
   file_dir varchar2(200) not null,
   item_category varchar2(200) not null
);
select * from java_item;
  
   
   drop table java_item cascade constraints;
   
 insert into JAVA_ITEM values(item_no_seq.nextval,'java',15000,'의현이가 좋아하는 랜덤 게임',
'황의현',sysdate,'http://www.naver.com');
insert into JAVA_ITEM values(item_no_seq.nextval,'java',15000,'지수가 좋아하는 랜덤 게임',
'양지수',sysdate,'http://www.naver.com');
insert into JAVA_ITEM values(item_no_seq.nextval,'jquery',15000,'용현이가 좋아하는 랜덤 게임',
'조용현',sysdate,'http://www.naver.com');
insert into JAVA_ITEM values(item_no_seq.nextval,'jquery',15000,'의현이가 좋아하는 랜덤 게임',
'황의현',sysdate,'http://www.naver.com');

insert into JAVA_ITEM values(item_no_seq.nextval,'jdbc',15000,'지수가 좋아하는 랜덤 게임',
'jdbc',sysdate,'http://www.naver.com');

insert into JAVA_ITEM values(item_no_seq.nextval,'java',15000,'의현이가 좋아하는 랜덤 게임',
'java',sysdate,'http://www.naver.com');

select * from JAVA_ITEM;
 	 		select item_no_seq.nextval from dual

drop table cart

create table cart(
   member_id varchar2(30) not null,
   item_no number not null,
   constraint fk_member_cart foreign key(member_id) references member(member_id),
   constraint fk_item_no_cart foreign key(item_no) references java_item(item_no)
)

insert into JAVA_ITEM
 constraint fk_member_id foreign key(member_id) references department(member_id);
 
 insert into cart(member_id,item_no) values('java',27);
 
 create table post(
 	item_no number not null,
 	item_name varchar2(100) not null,
 	post_title varchar2(100) not null,
 	member_id varchar2(100) not null,
 	post_content varchar2(1000),
	constraint fk_member_post foreign key(member_id) references member(member_id),
   constraint fk_item_no_post foreign key(item_no) references java_item(item_no)
 )
 select * from post;
 
 insert into post(item_no,post_title,member_id,post_content)

 drop table post;


		select item_no, item_name, item_price, item_info , member_id   from java_item where item_name = 'mentohwang';

-- 카테고리 테이블

		create table item_category(
			item_no number not null,
			category varchar2(50) not null,
			constraint fk_item_no_category foreign key(item_no) references java_item(item_no)
		)
		
		select * from JAVA_ITEM where item_category like '%mentohwang%';

drop table item_category

		select c.member_id, c.item_no, i.item_no, i.item_name, i.item_price, i.item_info , i.member_id
 		from cart c, java_item i
 		where i.member_id=c.member_id and i.item_no='1';

 	--
 		select c.member_id, c.item_no, i.item_no, i.item_name, i.item_price, i.item_info , i.member_id
 		from cart c, java_item i
 		where i.item_no=c.item_no and i.item_no='4';